Hello,

Would it help if you added a field to calculate?

your SQL: select ID, DATE, PRODUCT, sum(SALES) as SALES , 1 as counter as  from xx group by ID, DATE, PRODUCT

Change your formula in pivot to: sum(sales) / sum(counter)

Hope it helps,

Wouter

Hello Wouter,

Thank you for your solution. But it only can work properly when there are three group columns(include the ID itself); If the group columns greater than three, when I create the pivottable, some columns will become the Filter, then your method will not work properly.

Best Regards,

Sniperhgy

Dear All,

I have an excel file, in one sheet, it has four columns : ID (character type), DATE (character type), PRODUCT (character type) and SALES (numeric type). The data in this sheet is on ID, DATE and PRODUCT level (select ID, DATE, PRODUCT, sum(SALES) as SALES from xx group by ID, DATE, PRODUCT). Then I created a PivotTable for this tab, I drag the DATE to "Row Labels", PRODUCT to "Column Labels", ID to "Values" then get "Count of ID" and SALES to "Values" then get "Sum of SALES".

Now I want another filed : Total Sales / Number of unique ID in each DATE and PRODUCT group, so I added a calculated filed, the formula is SUM(SALES) / COUNT(ID), but the values are the same as the "Sum of SALES". The Excel 2007 calculated the COUNT(ID) as 1.

Could somebody help to solve this problem ? Thank you very much in advanced.

Best Regards,

Sniperhgy

• Edited by sniperhgy Tuesday, May 26, 2015 6:54 AM
• Moved by Lanlan HuangMicrosoft contingent staff 4 hours 9 minutes ago

Hi Sniperhgy,

According to your description, this issue is more related to PivotTable in excel, the Excel IT Pro Discussion forum is a better place for this issue, we will move it there for you.

Regards

Starain